package com.css.fxfzaqbz.modules.callJcData.repository.impl;

import cn.hutool.core.date.DateUtil;
import com.css.fxfzaqbz.constants.YNEnum;
import com.css.fxfzaqbz.modules.callJcData.entity.BasePageParam;
import com.css.fxfzaqbz.modules.callJcData.entity.SysDataVersion;
import com.css.fxfzaqbz.modules.callJcData.param.SysDataVersionPageParam;
import com.css.fxfzaqbz.modules.callJcData.param.SysDataVersionSameTypeDataExistParam;
import com.css.fxfzaqbz.modules.callJcData.param.SysDataVersionSaveMetaDataInfoParam;
import com.css.fxfzaqbz.modules.callJcData.param.SysDataVersionSynthesisSearchParam;
import com.css.fxfzaqbz.modules.supergraph.vo.VersionVO;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.*;

@Repository
public class SysDataVersionRepository {
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Resource
    private JdbcTemplate jdbcTemplate;

    private final static String TABLE_NAME = "sys_data_version"; // 表明
    private final static String TABLE_ID_KEY = "id"; // 表id字段名

    /**
     * 新增
     *
     * @param sysDataVersion
     */
    public void save(SysDataVersion sysDataVersion) {
        String sql = "insert into " + TABLE_NAME +
                " values(" +
                ":id, " +
                ":versionCode, " +
                ":versionTime, " +
                ":versionDesc, " +
                ":attachmentIds, " +
                ":uploadUser, " +
                ":updateDesc, " +
                ":dataRangeCountry, " +
                ":dataRangeProvince," +
                ":dataRangeCity," +
                ":dataName," +
                ":dataDesc," +
                ":uploadTime," +
                ":dataTypeLevel1Code," +
                ":dataTypeLevel2," +
                ":delFlag," +
                ":dataFileIds," +
                ":dataRangeArea, " +
                ":dataTypeLevel3, " +
                ":uploadSource, " +
                ":isSpatialData, " +
                ":spatialDataType, " +
                ":projectionInfo, " +
                ":fieldNum, " +
                ":fieldEntry," +
                ":updateTime," +
                ":updateUser," +
                ":dataRangeStr," +
                ":createTime," +
                ":createUser" +
                " )";
        SqlParameterSource source = new BeanPropertySqlParameterSource(sysDataVersion);
        namedParameterJdbcTemplate.update(sql, source);
    }

    /**
     * 根据一级数据分类获取版本号列表
     *
     * @param dataType 一级数据分类
     * @return
     */
    public List<String> getVersionCodeListByType(String dataType) {

        String sql = "select version_code from " + TABLE_NAME + " where data_type_level1_code = '" + dataType + "' and del_flag = '0' order by create_time desc";
        return jdbcTemplate.queryForList(sql, String.class);
    }

    /**
     * 根据id修改数据
     *
     * @param sysDataVersion
     */
    public void updateById(SysDataVersion sysDataVersion) {
        String sql = "update " + TABLE_NAME + " set " +
                "version_code = :versionCode," +
                "version_time = :versionTime," +
                "version_desc = :versionDesc," +
                "attachment_ids = :attachmentIds," +
                "upload_user = :uploadUser," +
                "update_desc = :updateDesc," +
                "data_range_country = :dataRangeCountry," +
                "data_range_province = :dataRangeProvince," +
                "data_range_city = :dataRangeCity," +
                "data_name = :dataName," +
                "data_desc = :dataDesc," +
                "upload_time = :uploadTime," +
                "data_type_level1_code = :dataTypeLevel1Code," +
                "data_type_level2 = :dataTypeLevel2," +
                "del_flag = :delFlag," +
                "data_file_ids = :dataFileIds," +
                "data_range_area = :dataRangeArea," +
                "data_type_level3 = :dataTypeLevel3," +
                "upload_source = :uploadSource," +
                "is_spatial_data = :isSpatialData," +
                "spatial_data_type = :spatialDataType," +
                "projection_info = :projectionInfo," +
                "field_num = :fieldNum," +
                "field_entry = :fieldEntry," +
                "update_time = :updateTime," +
                "update_user = :updateUser," +
                "data_range_str = :dataRangeStr" +
                " where " + TABLE_ID_KEY + " = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(sysDataVersion);
        namedParameterJdbcTemplate.update(sql, source);
    }

    public void delByIds(List<String> ids, String delUser, Date delTime) {
        String format = DateUtil.format(delTime, "yyyy-MM-dd HH:mm:ss");
        String sql = "update " + TABLE_NAME + " set del_flag = '1', update_time = '" + format + "', update_user = '" + delUser + "' where id in ('" + StringUtils.join(ids, "','") + "');";
        jdbcTemplate.update(sql);
    }

    public List<SysDataVersion> page(SysDataVersionPageParam sysDataVersionPageParam) {
        StringBuilder sqlBuilder = new StringBuilder("select * from " + TABLE_NAME + " where 1=1 ");
        pageSQLBuilder(sysDataVersionPageParam, sqlBuilder);
        sqlBuilder.append("and del_flag = '0'");
        sqlBuilder.append(" order by create_time desc");
        if (sysDataVersionPageParam.getPage() != null && sysDataVersionPageParam.getRows() != null) {
            this.pageSQLAppend(sqlBuilder, sysDataVersionPageParam);
        }
        return jdbcTemplate.query(sqlBuilder.toString(), new BeanPropertyRowMapper<>(SysDataVersion.class));
    }

    public void pageSQLAppend(StringBuilder sqlBuilder, BasePageParam basePageParam) {
        sqlBuilder.append(" limit ").append(basePageParam.getRows()).append(" offset ").append(basePageParam.getPage());
    }

    public Integer countForPage(SysDataVersionPageParam sysDataVersionPageParam) {
        StringBuilder sqlBuilder = new StringBuilder("select count(*) from " + TABLE_NAME + " where 1=1 ");
        pageSQLBuilder(sysDataVersionPageParam, sqlBuilder);
        sqlBuilder.append("and del_flag = '0'");
        return jdbcTemplate.queryForObject(sqlBuilder.toString(), Integer.class);
    }

    /**
     * 根据查询参数构建分页查询sql的
     *
     * @param param
     * @param sqlBuilder
     */
    private void pageSQLBuilder(SysDataVersionPageParam param, StringBuilder sqlBuilder) {
        if (param.getVersionStartTime() != null && param.getVersionEndTime() != null) {
            sqlBuilder.append("and version_time between '").append(DateUtil.format(param.getVersionStartTime(), "yyyy-MM-dd")).append("' and '").append(DateUtil.format(param.getVersionEndTime(), "yyyy-MM-dd")).append("' ");
        }
        if (param.getUploadStartTime() != null && param.getUploadEndTime() != null) {
            sqlBuilder.append("and upload_time between '").append(DateUtil.format(param.getUploadStartTime(), "yyyy-MM-dd")).append("' and '").append(DateUtil.format(param.getUploadEndTime(), "yyyy-MM-dd")).append("' ");
        }
        if (StringUtils.isNotBlank(param.getVersionCode())) {
            sqlBuilder.append("and version_code like '%").append(param.getVersionCode().trim()).append("%' ");
        }
        if (StringUtils.isNotBlank(param.getDataName())) {
            sqlBuilder.append("and data_name like '%").append(param.getDataName().trim()).append("%' ");
        }
        if (StringUtils.isNotBlank(param.getDataType())) {
            sqlBuilder.append("and data_type_level2 = '").append(param.getDataType()).append("' ");
        }

        if (param.getDataRangeCountry() != null) {
            sqlBuilder.append("and data_range_country = '").append(param.getDataRangeCountry()).append("' ");
        }
        if (StringUtils.isNotBlank(param.getDataRangeProvince())) {
            sqlBuilder.append("and data_range_province = '").append(param.getDataRangeProvince()).append("' ");
        }
        if (StringUtils.isNotBlank(param.getDataRangeCity())) {
            sqlBuilder.append("and data_range_city = '").append(param.getDataRangeCity()).append("' ");
        }
        if (StringUtils.isNotBlank(param.getDataRangeArea())) {
            sqlBuilder.append("and data_range_area = '").append(param.getDataRangeArea()).append("' ");
        }
        if (StringUtils.isNotBlank(param.getDataTypeCode())) {
            sqlBuilder.append("and data_type_level1_code = '").append(param.getDataTypeCode()).append("' ");
        }
    }

    public List<SysDataVersion> selectByIds(List<String> idList) {
        StringBuilder sqlBuilder = new StringBuilder("SELECT * from " + TABLE_NAME + " where " + TABLE_ID_KEY + " IN ('");
        for (String id : idList) {
            sqlBuilder.append(id).append("', '");
        }
        sqlBuilder.append("') order by create_time desc");
        return jdbcTemplate.query(sqlBuilder.toString().replaceAll("\"", ""), new BeanPropertyRowMapper<>(SysDataVersion.class));
    }

    public void saveMetaDataInfo(SysDataVersionSaveMetaDataInfoParam param) {
        String sql = "update " + TABLE_NAME + " set " +
                "upload_source = :uploadSource," +
                "spatial_data_type = :spatialDataType," +
                "projection_info = :projectionInfo," +
                "field_num = :fieldNum," +
                "field_entry = :fieldEntry," +
                "update_time = :updateTime," +
                "update_user = :updateUser" +
                " where version_code" + " = :versionCode";
        SqlParameterSource source = new BeanPropertySqlParameterSource(param);
        namedParameterJdbcTemplate.update(sql, source);
    }

    /**
     * 根据版本号获取元数据信息
     *
     * @param versionCode
     * @return
     */
    public SysDataVersion getByVersionCode(String versionCode) {
        String sql = "select * from " + TABLE_NAME + " where version_code = '" + versionCode + "'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(SysDataVersion.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    /**
     * 根据版本号删除
     *
     * @param versionCode
     */
    public void realDelByVersionCode(String versionCode) {
        jdbcTemplate.update(new StringBuilder("delete from " + TABLE_NAME + " where version_code" + " = '").append(versionCode).append("'").toString());
    }

    /**
     * 综合检索总条数查询
     *
     * @param param
     * @return
     */
    public Integer countForSynthesisSearch(SysDataVersionSynthesisSearchParam param) {
        StringBuilder sqlBuilder = new StringBuilder("select count(*) from sys_data_version as sdv left join sys_dict_item as sdi on sdv.data_type_level2 = sdi.dict_item_id where 1=1 ");
        this.synthesisSearchSQLBuilder(param, sqlBuilder);
        sqlBuilder.append("and del_flag = '0'");
        return jdbcTemplate.queryForObject(sqlBuilder.toString(), Integer.class);
    }

    /**
     * 综合检索
     *
     * @param param
     * @return
     */
    public List<SysDataVersion> synthesisSearch(SysDataVersionSynthesisSearchParam param) {
        StringBuilder sqlBuilder = new StringBuilder("select sdv.* from sys_data_version as sdv left join sys_dict_item as sdi on sdv.data_type_level2 = sdi.dict_item_id where 1=1 ");
        this.synthesisSearchSQLBuilder(param, sqlBuilder);
        sqlBuilder.append("and del_flag = '0' order by create_time desc ");
        sqlBuilder.append(" limit ").append(param.getRows()).append(" offset ").append(param.getPage());
        return jdbcTemplate.query(sqlBuilder.toString(), new BeanPropertyRowMapper<>(SysDataVersion.class));
    }

    /**
     * 根据查询参数构建分页查询sql的
     *
     * @param param
     * @param sqlBuilder
     */
    private void synthesisSearchSQLBuilder(SysDataVersionSynthesisSearchParam param, StringBuilder sqlBuilder) {
        pageSQLBuilder(param, sqlBuilder);
        if (StringUtils.isNotBlank(param.getKeyword())) {
            sqlBuilder.append("and (")
                    .append("sdv.version_code like '%") // 版本号
                    .append(param.getKeyword().trim())
                    .append("%' or ")
                    .append("sdv.version_desc like '%") // 版本说明
                    .append(param.getKeyword().trim())
                    .append("%' or ")
                    .append("sdv.data_range_str like '%") // 数据范围
                    .append(param.getKeyword().trim())
                    .append("%' or ")
                    .append("sdv.data_name like '%") // 数据名称
                    .append(param.getKeyword().trim())
                    .append("%' or ")
                    .append("sdv.data_desc like '%") // 数据说明
                    .append(param.getKeyword().trim())
                    .append("%' or ")
                    .append("sdv.update_desc like '%") // 更新说明
                    .append(param.getKeyword().trim())
                    .append("%' or ")
                    .append("(TO_CHAR(sdv.upload_time, 'yyyy-MM-dd') LIKE '%" + param.getKeyword() + "%')") // 上传时间
                    .append(" or ")
                    .append("(TO_CHAR(sdv.version_time, 'yyyy-MM-dd') LIKE '%" + param.getKeyword() + "%')") // 版本时间
                    .append(" or ")
                    .append("sdi.dict_item_name like '%") // 数据类别
                    .append(param.getKeyword().trim())
                    .append("%' )")
            ;
        }
    }

    /**
     * 判断是否已有同类数据
     * 判断依据：如果选择的一样的数据类别且一样的数据范围
     *
     * @param sysDataVersionSameTypeDataExistParam
     * @return
     */
    public String sameTypeDataExist(SysDataVersionSameTypeDataExistParam sysDataVersionSameTypeDataExistParam) {
        StringBuilder sqlBuilder = new StringBuilder("select version_code from " + TABLE_NAME + " where 1=1 ");
        sqlBuilder.append(" and data_type_level1_code = '").append(sysDataVersionSameTypeDataExistParam.getDataTypeLevel1()).append("'");
        sqlBuilder.append(" and data_range_country = ").append(sysDataVersionSameTypeDataExistParam.getDataRangeCountry());
        if (StringUtils.isNotBlank(sysDataVersionSameTypeDataExistParam.getDataRangeProvince())) {
            sqlBuilder.append(" and data_range_province = '").append(sysDataVersionSameTypeDataExistParam.getDataRangeProvince()).append("'");
        }
        if (StringUtils.isNotBlank(sysDataVersionSameTypeDataExistParam.getDataRangeCity())) {
            sqlBuilder.append(" and data_range_city = '").append(sysDataVersionSameTypeDataExistParam.getDataRangeCity()).append("'");
        }
        if (StringUtils.isNotBlank(sysDataVersionSameTypeDataExistParam.getDataRangeArea())) {
            sqlBuilder.append(" and data_range_area = '").append(sysDataVersionSameTypeDataExistParam.getDataRangeArea()).append("'");
        }
        sqlBuilder.append(" and del_flag = '0'");
        sqlBuilder.append(" order by upload_time desc");
        sqlBuilder.append(" limit ").append(1).append(" offset ").append(0);
        try {
            return jdbcTemplate.queryForObject(sqlBuilder.toString(), String.class);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public String getVersionCodeById(String id) {
        String sql = "select version_code from " + TABLE_NAME + " where id = '" + id + "'";
        try {
            return jdbcTemplate.queryForObject(sql, String.class);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    /**
     * @param dateCode 临时版本号
     * @param name     表名
     */
    public void saveTemporaryDateByDateCode(String dateCode, String dataVersion, String name) {
        String sql = " update " + name + " set data_version = '" + dataVersion + "' where data_version = '" + dateCode + "'";
        jdbcTemplate.update(sql);
    }

    /**
     * 根据版本号删除业务数据
     *
     * @param dataVersion 版本号
     * @param tableName   业务表名
     */
    public void delBizDataByDataVersion(String dataVersion, String tableName) {
        String sql = "update " + tableName + " set del_flag = '1' where data_version='" + dataVersion + "'";
        jdbcTemplate.update(sql);
    }

    /**
     * 删除临时数据,根据版本号
     *
     * @param dateCode  临时版本号
     * @param tableName 表名
     */
    public void deleteTemporaryDateByDateCode(String dateCode, String tableName) {
        String sql = "delete from " + tableName + " where data_version = '" + dateCode + "'";
        jdbcTemplate.update(sql);
    }

    public SysDataVersion selectById(String id) {
        String sql = "select * from " + TABLE_NAME + " where id = '" + id + "'";
        try {
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(SysDataVersion.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public String getCount() {
        StringBuilder sqlBuilder = new StringBuilder("select count(*) from "+TABLE_NAME+" where 1=1  ");
        try {
            Integer a = jdbcTemplate.queryForObject(sqlBuilder.toString(), Integer.class);
            return String.valueOf(a);
        } catch (DataAccessException e) {
            return null;
        }
    }

    public List<SysDataVersion> getDataVersionView(SysDataVersionPageParam param) {
        StringBuilder sqlBuilder=new StringBuilder("select * from sys_data_version where 1=1 and del_flag='0' ");
        if (StringUtils.isNotBlank(param.getDataTypeCode())){
            sqlBuilder.append("and data_type_level1_code = '").append(param.getDataTypeCode()).append("' ");
        }
        if (StringUtils.isNotBlank(String.valueOf(param.getDataRangeCountry()))) {
            sqlBuilder.append("and data_range_country = '").append(param.getDataRangeCountry()).append("' ");
        }
        if (StringUtils.isNotBlank(param.getDataRangeProvince())) {
            sqlBuilder.append("and data_range_province = '").append(param.getDataRangeProvince()).append("' ");
        }
        if (StringUtils.isNotBlank(param.getDataRangeCity())) {
            sqlBuilder.append("and data_range_city = '").append(param.getDataRangeCity()).append("' ");
        }
        if (StringUtils.isNotBlank(param.getDataRangeArea())) {
            sqlBuilder.append("and data_range_area = '").append(param.getDataRangeArea()).append("' ");
        }
        if (StringUtils.isNotBlank(param.getVersionCode())){
            sqlBuilder.append("and version_code like  '%").append(param.getVersionCode()).append( "%' ");

        }
        return jdbcTemplate.query(sqlBuilder.toString(), new BeanPropertyRowMapper<>(SysDataVersion.class));

    }

    public Map<String, Object> getFyDataView(VersionVO versionVO) {
       /*List<District> list = versionVO.getList();
        List<String> pList=new ArrayList<>();
        List<String> cList=new ArrayList<>();
        List<String> xList=new ArrayList<>();
        for (District district : list) {
            if (StringUtils.isNotBlank(district.getProvinceId())){
                pList.add(district.getProvinceId());
            }
            if (StringUtils.isNotBlank(district.getCityId())){
                cList.add(district.getCityId());
            }
            if (StringUtils.isNotBlank(district.getPrefectureId())){
                xList.add(district.getPrefectureId());
            }
        }
        String s = pList.get(0);
        Map<String,Object> paramMap=new HashMap<>();
        paramMap.put("cList",cList);
        paramMap.put("xList",xList);*/
        StringBuilder sql=new StringBuilder();
        sql.append("SELECT * FROM  sys_data_version  where 1=1 and del_flag='"+ YNEnum.N +"'and data_type_level1_code = '1'");
        int firstIndex=(Integer.parseInt(versionVO.getPage())-1) * Integer.parseInt(versionVO.getRows());
        int lastIndex=Integer.parseInt(versionVO.getRows());
        sql.toString();
        StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SysDataVersion.class)).size();
        List<SysDataVersion> query = jdbcTemplate.query(sql.append(limitSql).toString(),new BeanPropertyRowMapper<>(SysDataVersion.class));

        Map<String,Object> map=new HashMap();
        map.put("total",total);
        map.put("rows",query);
        return map;
    }
}
